package org.footito.android.data.database;

import org.footito.android.tools.Settings;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

class DatabaseAdapter extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "footito";

	/* Informations for table Teams */
	public static final String TABLE_TEAMS = "teams";
	public static final String COL_TEAM_ID = "_id";
	public static final String COL_TEAM_NAME = "team_name";
	public static final String COL_TEAM_LINK = "team_link";
	public static final String COL_TEAM_PICTURE = "team_picture";
	public static final int COLNUM_TEAM_ID = 0;
	public static final int COLNUM_TEAM_NAME = 1;
	public static final int COLNUM_TEAM_LINK = 2;
	public static final int COLNUM_TEAM_PICTURE = 3;
	private static final String CREATE_TABLE_TEAMS = "CREATE TABLE " + TABLE_TEAMS + "( " + COL_TEAM_ID + " integer primary key, " + COL_TEAM_NAME
			+ " TEXT NOT NULL, " + COL_TEAM_LINK + " TEXT NOT NULL, " + COL_TEAM_PICTURE + " TEXT NOT NULL);";
	public static final String REQUEST_ONE_TEAM = COL_TEAM_ID + " = ?";

	/* Informations for table Events */
	public static final String TABLE_EVENTS = "events";
	public static final String COL_EVENT_ID = "_id";
	public static final String COL_EVENT_TYPE = "event_type";
	public static final String COL_EVENT_IDTEAM = "event_idTeam";
	public static final String COL_EVENT_TIMESTAMP = "event_timestamp";
	public static final String COL_EVENT_CONTENT = "event_content";
	public static final String COL_EVENT_LINK = "event_link";
	public static final int COLNUM_EVENT_ID = 0;
	public static final int COLNUM_EVENT_TYPE = 1;
	public static final int COLNUM_EVENT_IDTEAM = 2;
	public static final int COLNUM_EVENT_TIMESTAMP = 3;
	public static final int COLNUM_EVENT_CONTENT = 4;
	public static final int COLNUM_EVENT_LINK = 5;
	private static final String CREATE_TABLE_EVENTS = "CREATE TABLE " + TABLE_EVENTS + "( " + COL_EVENT_ID + " integer primary key, "
			+ COL_EVENT_TYPE + " TEXT NOT NULL, " + COL_EVENT_IDTEAM + " integer NOT NULL, " + COL_EVENT_TIMESTAMP + " TEXT NOT NULL, "
			+ COL_EVENT_CONTENT + " TEXT NOT NULL, " + COL_EVENT_LINK + " TEXT NOT NULL);";
	public static final String REQUEST_ONE_EVENT = COL_EVENT_ID + " = ?";
	public static final String REQUEST_EVENTS_BY_TEAM = COL_EVENT_IDTEAM + " = ?";

	/* Informations for table Subscribers */
	public static final String TABLE_SUBSCRIBERS = "subscribers";
	public static final String COL_SUBSCRIBER_ID = "_id";
	public static final String COL_SUBSCRIBER_IDEVENT = "subscriber_idEvent";
	public static final String COL_SUBSCRIBER_IDTEAM = "subscriber_idTeam";
	public static final int COLNUM_SUBSCRIBER_ID = 0;
	public static final int COLNUM_SUBSCRIBER_IDEVENT = 1;
	public static final int COLNUM_SUBSCRIBER_IDTEAM = 2;
	private static final String CREATE_TABLE_SUBSCRIBERS = "CREATE TABLE " + TABLE_SUBSCRIBERS + "( " + COL_SUBSCRIBER_ID + " integer primary key, "
			+ COL_SUBSCRIBER_IDEVENT + " integer NOT NULL, " + COL_SUBSCRIBER_IDTEAM + " integer NOT NULL);";
	public static final String REQUEST_SUBS_BY_EVENT = COL_SUBSCRIBER_IDEVENT + " = ?";
	public static final String REQUEST_ONE_SUBSCRIBER = COL_SUBSCRIBER_ID + " = ?";

	/* Informations for table Comments */
	public static final String TABLE_COMMENTS = "comments";
	public static final String COL_COMMENT_ID = "_id";
	public static final String COL_COMMENT_IDEVENT = "comment_idEvent";
	public static final String COL_COMMENT_IDTEAM = "comment_idTeam";
	public static final String COL_COMMENT_CONTENT = "comment_content";
	public static final int COLNUM_COMMENT_ID = 0;
	public static final int COLNUM_COMMENT_IDEVENT = 1;
	public static final int COLNUM_COMMENT_CONTENT = 2;
	public static final int COLNUM_COMMENT_IDTEAM = 3;
	private static final String CREATE_TABLE_COMMENTS = "CREATE TABLE " + TABLE_COMMENTS + "( " + COL_COMMENT_ID + " integer primary key, "
			+ COL_COMMENT_IDEVENT + " integer NOT NULL, " + COL_COMMENT_CONTENT + " TEXT NOT NULL, " + COL_COMMENT_IDTEAM + " integer NOT NULL);";
	public static final String REQUEST_COMS_BY_EVENT = COL_COMMENT_IDEVENT + " = ?";
	public static final String REQUEST_ONE_COMMENT = COL_COMMENT_ID + " = ?";

	private static final String TAG = "DBAdapter";

	private static final int DATABASE_VERSION = 3;
	private Context context;
	private SQLiteDatabase db;

	public DatabaseAdapter(Context ctx) {
		super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
		context = ctx;

		Log.v(TAG, "curDbVersion = " + DATABASE_VERSION);

		open();
	}

	public void close() {
		db.close();
	}

	public Cursor getAllCommentsCursor(String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_COMMENTS, projection, selection, selectionArgs, null, null, COL_COMMENT_ID + " ASC");
	}

	public Cursor getAllEventsCursor(String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_EVENTS, projection, selection, selectionArgs, null, null, COL_EVENT_TIMESTAMP + " DESC");
	}

	public Cursor getAllSubscribersCursor(String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_SUBSCRIBERS, projection, selection, selectionArgs, null, null, COL_SUBSCRIBER_ID + " DESC");
	}

	public Cursor getAllTeamsCursor(String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_TEAMS, projection, selection, selectionArgs, null, null, COL_TEAM_ID + " DESC");
	}

	public Cursor getCommentCursor(int id) {
		String selection = REQUEST_ONE_COMMENT;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_COMMENTS, null, selection, selectionArgs, null, null, COL_COMMENT_ID + " ASC");
	}

	public Cursor getEventCursor(int id) {
		String selection = REQUEST_ONE_EVENT;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_EVENTS, null, selection, selectionArgs, null, null, null);
	}

	public Cursor getSubscriberCursor(int id) {
		String selection = REQUEST_ONE_SUBSCRIBER;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_SUBSCRIBERS, null, selection, selectionArgs, null, null, null);
	}

	public Cursor getTeamCursor(int id) {
		String selection = REQUEST_ONE_TEAM;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_TEAMS, null, selection, selectionArgs, null, null, null);
	}

	public long insertComment(ContentValues values) {
		return db.insert(TABLE_COMMENTS, null, values);
	}

	public long insertEvent(ContentValues values) {
		return db.insert(TABLE_EVENTS, null, values);
	}

	public long insertSubscriber(ContentValues values) {
		return db.insert(TABLE_SUBSCRIBERS, null, values);
	}

	public long insertTeam(ContentValues values) {
		try {
			return db.insert(TABLE_TEAMS, null, values);
		} catch (Exception e) {
			Log.e("Exception", e.getClass().toString() + ": " + e.getMessage());
			return -1;
		}
	}

	public boolean isOpen() {
		if (db == null)
			return false;
		return db.isOpen();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_TEAMS);
		db.execSQL(CREATE_TABLE_EVENTS);
		db.execSQL(CREATE_TABLE_COMMENTS);
		db.execSQL(CREATE_TABLE_SUBSCRIBERS);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SUBSCRIBERS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
		Settings.resetChecks(context);
		onCreate(db);
	}

	public void open() {
		// create the dbHelper
		if (db != null && db.isOpen()) {
			return;
		}
		db = this.getWritableDatabase();
	}

	public int removeComment(int id) {
		return db.delete(TABLE_COMMENTS, COL_COMMENT_ID + " = " + id, null);
	}

	public int removeComments(String selection, String[] selectionArgs) {
		return db.delete(TABLE_COMMENTS, selection, selectionArgs);
	}

	public int removeEvent(int id) {
		return db.delete(TABLE_EVENTS, COL_EVENT_ID + " = " + id, null);
	}

	public int removeEvents(String where, String[] whereArgs) {
		return db.delete(TABLE_EVENTS, where, whereArgs);
	}

	public int removeSubscriber(int id) {
		return db.delete(TABLE_SUBSCRIBERS, COL_SUBSCRIBER_ID + " = " + id, null);
	}

	public int removeSubscribers(String selection, String[] selectionArgs) {
		return db.delete(TABLE_SUBSCRIBERS, selection, selectionArgs);
	}

	public int removeTeam(int id) {
		return db.delete(TABLE_TEAMS, COL_TEAM_ID + " = " + id, null);
	}

	public int removeTeams(String selection, String[] selectionArgs) {
		return db.delete(TABLE_TEAMS, selection, selectionArgs);
	}

	public int updateComment(ContentValues values, String string, String[] selectionArgs) {
		return db.update(TABLE_COMMENTS, values, string, selectionArgs);
	}

	public int updateEvent(ContentValues values, String selection, String[] selectionArgs) {
		return db.update(TABLE_EVENTS, values, selection, selectionArgs);
	}

	public int updateSubscriber(ContentValues values, String string, String[] selectionArgs) {
		return db.update(TABLE_SUBSCRIBERS, values, string, selectionArgs);
	}

	public int updateTeam(ContentValues values, String string, String[] selectionArgs) {
		return db.update(TABLE_TEAMS, values, string, selectionArgs);
	}

}
